﻿<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>PROCEDURE</title>
<meta name="GENERATOR" content="WinCHM">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

</head>

<body>
<P><A 
href="http://msdn.microsoft.com/en-us/library/ms187926.aspx"><FONT 
face=Courier>http://msdn.microsoft.com/en-us/library/ms187926.aspx</FONT></A></P>
<P><FONT ><FONT face=Courier><STRONG>CREATE PROCEDURE (Transact-SQL)</STRONG><BR></FONT></FONT></P>
<P><FONT face=Courier></FONT>&nbsp;</P>
<P><A 
href="http://neitcouq.blogspot.com/2010/02/gioi-thieu-ve-stored-procedure-trong_02.html"><FONT 
face=Courier>http://neitcouq.blogspot.com/2010/02/gioi-thieu-ve-stored-procedure-trong_02.html</FONT></A></P>
<P><FONT face=Courier>&nbsp;DELIMITER //<BR>CREATE PROCEDURE 
GetAllProducts()<BR>&nbsp; BEGIN<BR>&nbsp; SELECT *&nbsp; FROM 
products;<BR>&nbsp; END //<BR>DELIMITER ;</FONT></P>
<P><FONT face=Courier></FONT>&nbsp;</P>
<P><FONT face=Courier>DROP PROCEDURE 
GetAllProducts</FONT>  </P>
<P><FONT face=Courier></FONT>  &nbsp;</P>
<P><FONT face=Courier></FONT>  &nbsp;</P>
<P><FONT face=Courier>DROP PROCEDURE 
`selectUserIdForCreateGameByRandom`</FONT>  </P>
<P><FONT face=Courier>DELIMITER //<BR>CREATE PROCEDURE 
selectUserIdForCreateGameByRandom(UID INT)<BR>&nbsp; BEGIN<BR>SELECT <FONT 
size=2>id,&nbsp;username, facebook_id, twitter_id, avatar</FONT>  &nbsp;FROM 
`m_user` <BR>WHERE<BR>  </FONT><FONT face=Courier>`id` &lt;&gt; UID <BR>  </FONT><FONT face=Courier>AND<BR>`id` NOT IN <BR>( <BR>&nbsp;&nbsp;&nbsp;&nbsp; SELECT 
`fr_user_id`<BR>&nbsp;&nbsp;&nbsp;&nbsp; FROM 
`m_data`<BR>&nbsp;&nbsp;&nbsp;&nbsp; WHERE `to_user_id`=UID <BR>)<BR>AND 
<BR>`id` NOT IN <BR>( <BR>&nbsp;&nbsp;&nbsp;&nbsp; SELECT 
`to_user_id`<BR>&nbsp;&nbsp;&nbsp;&nbsp; FROM 
`m_data`<BR>&nbsp;&nbsp;&nbsp;&nbsp; WHERE `fr_user_id`=UID <BR>) <BR>ORDER BY 
Rand( ) LIMIT 1;<BR>&nbsp; END //<BR> DELIMITER ;</FONT>  </P>
<P><FONT face=Courier>CALL 
selectUserIdForCreateGameByRandom(156)</FONT> 
</P>
<P> 
</P>
<P> 
</P>
<P> 
</P>
<P><FONT face=Courier>DROP PROCEDURE `<FONT 
face="Times New Roman">getFriends</FONT>`</FONT> 
</P>
<P>DELIMITER //<BR>CREATE PROCEDURE getFriends(IN UID 
INT,&nbsp;&nbsp; IN FIELDS VARCHAR(255))<BR>&nbsp; BEGIN 
</P>
<P>SET @s = CONCAT('SELECT ' , FIELDS ,' 
FROM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; `m_user`&nbsp;&nbsp;&nbsp; 
WHERE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; `id`=',UID);&nbsp;&nbsp;&nbsp; 
<BR>PREPARE stmt FROM @s;&nbsp;&nbsp;&nbsp; <BR>EXECUTE stmt; 
</P>
<P>&nbsp; END //<BR>DELIMITER ; 
</P>
<P><FONT face=Courier>CALL 
getFriends(156,'ID,USERNAME')</FONT>  
</P>
<P>  
</P>
<P>  
</P>
<P>DELIMITER //<BR>CREATE PROCEDURE getFriends(IN UID 
INT,&nbsp;&nbsp; IN FIELDS VARCHAR(255))<BR>&nbsp; BEGIN 
</P>
<P>SET @s = CONCAT('SELECT ' , FIELDS ,' FROM m_user INNER 
JOIN m_data ON&nbsp; m_user.id = m_data.fr_user_id WHERE m_data.to_user_id = 
',UID,' AND m_data.status2 &lt;&gt; -1' );&nbsp;&nbsp;&nbsp; <BR>PREPARE stmt 
FROM @s;&nbsp;&nbsp;&nbsp; <BR>EXECUTE stmt; 
</P>
<P><BR>&nbsp; END //<BR>DELIMITER ; 
</P>
<P>  
</P>
<P> 
-------------------------------------------------------------------- 
</P>
<P><FONT face=Courier>DELIMITER //<BR>CREATE PROCEDURE 
cleanUpUsers(Y INT)<BR>&nbsp; BEGIN<BR></FONT><FONT 
face=Courier><FONT 
face="Times New Roman">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DELETE 
FROM `m_user` 
WHERE&nbsp;&nbsp; NOW() - `logintime` &gt; Y*10000000000</FONT>  
   ;<BR>&nbsp; END </FONT><FONT face=Courier>  //<BR> DELIMITER ;</FONT>  
</P>
<P><FONT face=Courier>DELIMITER //<BR>CREATE PROCEDURE 
cleanUpUsers(Y INT)<BR>&nbsp; BEGIN<BR></FONT><FONT 
face=Courier><FONT face=Courier><FONT face=Courier>&nbsp;&nbsp;&nbsp;&nbsp; 
SELECT MINUTE(CURRENT_TIME) - MINUTE(`logintime`) FROM `m_user` WHERE 
`id`=156</FONT>  
   <BR>&nbsp; END </FONT><FONT 
face=Courier>  //<BR> DELIMITER ;</FONT></FONT><FONT 
face="Times New Roman"> </FONT>  
</P>
<P><FONT face=Courier>CALL cleanUpUsers(1)</FONT>  
</P>
<P><FONT face=Courier>DROP PROCEDURE `<FONT 
>  cleanUpUsers</FONT>`</FONT>  
</P>
<P>  
</P>
<P>SELECT  
</P>
<P><A 
style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; MARGIN: 0px; WORD-SPACING: 0px; FONT: 13px/25px Helvetica, Arial, sans-serif; VERTICAL-ALIGN: baseline; TEXT-TRANSFORM: none; BORDER-LEFT: 0px; COLOR: rgb(1,90,132); TEXT-INDENT: 0px; PADDING-TOP: 0px; BORDER-BOTTOM: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BACKGROUND-COLOR: rgb(255,255,255); TEXT-DECORATION: underline; orphans: 2; widows: 2; webkit-text-size-adjust: auto; webkit-text-stroke-width: 0px; outline: 0px" 
href="http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-sub"><CODE 
class=literal 
style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-WEIGHT: bold; FONT-SIZE: 13px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(2,103,137); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; FONT-FAMILY: 'Courier New', Courier, fixed, monospace; BACKGROUND-COLOR: white; TEXT-DECORATION: underline; outline: 0px">DATE_SUB</CODE></A>(NOW(),`logintime`)  
</P>
<P>FROM `m_user` WHERE 1  
</P>
<P>  
</P>
<P>  
</P>
<P>SELECT <BR>*<BR>FROM `m_user`  
</P>
<P>WHERE  
</P>
<P>NOW() - `logintime` &gt; 10000000000  
</P>
<P>  
</P>
<P>  
</P>
<P>  
</P>
<P>  
</P>
<P>10000105808  
</P>
<P>10 000 000 000  
</P>
<P>-------------------------------------------------------------------  
</P>
<P>  
</P>
<P>  
</P>
<P> 
</P>
<P> 
</P>
<P><A 
href="http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure">http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure</A> 
</P>
<P> 
</P><PRE class="lang-sql prettyprint" style="BORDER-RIGHT: 0px; PADDING-RIGHT: 5px; BORDER-TOP: 0px; PADDING-LEFT: 5px; PADDING-BOTTOM: 5px; MARGIN: 0px 0px 10px; WORD-SPACING: 0px; FONT: 14px/18px Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; VERTICAL-ALIGN: baseline; OVERFLOW: auto; TEXT-TRANSFORM: none; BORDER-LEFT: 0px; WIDTH: auto; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; PADDING-TOP: 5px; BORDER-BOTTOM: 0px; LETTER-SPACING: normal; BACKGROUND-COLOR: rgb(238,238,238); TEXT-ALIGN: left; orphans: 2; widows: 2; webkit-text-size-adjust: auto; webkit-text-stroke-width: 0px; max-height: 600px"><CODE style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; PADDING-TOP: 0px; BORDER-BOTTOM: 0px; FONT-FAMILY: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; BACKGROUND-COLOR: rgb(238,238,238)"><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">delimiter </SPAN><SPAN class=pun style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">//</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent"> <BR></SPAN><SPAN class=kwd style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,139); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">CREATE</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent"> </SPAN><SPAN class=kwd style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,139); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">PROCEDURE</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent"> dynamic</SPAN><SPAN class=pun style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">(</SPAN><SPAN class=kwd style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,139); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">IN</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent"> tbl CHAR</SPAN><SPAN class=pun style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">(</SPAN><SPAN class=lit style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(128,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">64</SPAN><SPAN class=pun style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">),</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent"> </SPAN><SPAN class=kwd style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,139); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">IN</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent"> col CHAR</SPAN><SPAN class=pun style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">(</SPAN><SPAN class=lit style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(128,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">64</SPAN><SPAN class=pun style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">))</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent"><BR></SPAN><SPAN class=kwd style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,139); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">BEGIN</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent"><BR>&nbsp; &nbsp; </SPAN><SPAN class=kwd style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,139); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">SET</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent"> </SPAN><SPAN class=pun style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">@</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">s </SPAN><SPAN class=pun style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">=</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent"> CONCAT</SPAN><SPAN class=pun style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">(</SPAN><SPAN class=str style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(128,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">'SELECT '</SPAN><SPAN class=pun style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">,</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">col</SPAN><SPAN class=pun style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">,</SPAN><SPAN class=str style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(128,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">' FROM '</SPAN><SPAN class=pun style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">,</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">tbl </SPAN><SPAN class=pun style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">);</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent"><BR>&nbsp; &nbsp; PREPARE stmt </SPAN><SPAN class=kwd style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,139); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">FROM</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent"> </SPAN><SPAN class=pun style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">@</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">s</SPAN><SPAN class=pun style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">;</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent"><BR>&nbsp; &nbsp; </SPAN><SPAN class=kwd style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,139); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">EXECUTE</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent"> stmt</SPAN><SPAN class=pun style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">;</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent"><BR></SPAN><SPAN class=kwd style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,139); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">END</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent"><BR></SPAN><SPAN class=pun style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">//</SPAN><SPAN class=pln style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent"><BR>delimiter </SPAN><SPAN class=pun style="BORDER-RIGHT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: 0px; PADDING-LEFT: 0px; FONT-SIZE: 14px; PADDING-BOTTOM: 0px; MARGIN: 0px; VERTICAL-ALIGN: baseline; BORDER-LEFT: 0px; COLOR: rgb(0,0,0); PADDING-TOP: 0px; BORDER-BOTTOM: 0px; BACKGROUND-COLOR: transparent">;</SPAN></CODE></PRE>
<P> 
</P>
<P> 
</P>
<P> 
</P>
<P> 
</P>
<P> 
</P>
<P>DELIMITER $$<BR>DROP PROCEDURE IF EXISTS 
WhileLoopProc$$<BR>CREATE PROCEDURE WhileLoopProc(L 
INT)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
BEGIN<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
DECLARE x&nbsp; INT; 
</P>
<P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
SET x = 1; 
</P>
<P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
WHILE x&nbsp; &lt;= L 
DO<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
INSERT INTO `t_comment`( `fr_user_id`, `to_user_id`, `comment` ) VALUES ( 1712, 
42 ,'test comment test comment test comment test comment test comment test 
comment test comment' ) ; 
</P>
<P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
SET&nbsp; x = x + 1; 
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
END 
WHILE;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
SELECT str;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END$$<BR>&nbsp; DELIMITER 
;<BR><BR> 
</P>
<P>&nbsp;<FONT face=Courier>CALL <FONT 
face="Times New Roman">WhileLoopProc</FONT>(1000)</FONT> 
</P>
<P>&nbsp; 
</P>
<P>&nbsp; 
</P>
<P>&nbsp; 
</P>
<P>&nbsp; 
</P>
<P> 
</P>
<P> 
DELIMITER $$&nbsp; <BR>CREATE PROCEDURE 
LOOP_ADD_COMMENT(L INT)<BR>&nbsp;&nbsp; BEGIN<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
DECLARE a INT Default 0 ;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; simple_loop: 
LOOP<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INSERT INTO 
`t_comment`( `fr_user_id`, `to_user_id`, `comment` ) VALUES ( 1712, 42 ,'test 
comment test comment test comment test comment test comment test comment test 
comment' ) 
</P>
<P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SET a =  
a+1;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select 
a;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IF a=L 
THEN<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LEAVE 
simple_loop;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END 
IF;<BR>&nbsp;&nbsp; END LOOP simple_loop;<BR>END $$ 
</P></body>
</html>
